{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "b930f418",
   "metadata": {},
   "source": [
    "# Table Explorer\n",
    "\n",
    "\n",
    "```{versionadded} 0.7.6\n",
    "~~~\n",
    "pip install jupysql --upgrade\n",
    "~~~\n",
    "```\n",
    "\n",
    "In this guide, we demonstrate how to use JupySQL's table explorer to visualize SQL tables in HTML format and interact with them efficiently. By running SQL queries in the background instead of loading the data into memory, we minimize the resource consumption and processing time required for handling large datasets, making the interaction with the SQL tables faster and more streamlined.\n",
    "\n",
    "\n",
    "Let's start by preparing our dataset. We'll be using the [NYC taxi dataset](https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page).\n",
    "\n",
    "## Download the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "67e9f89e",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pathlib import Path\n",
    "from urllib.request import urlretrieve\n",
    "\n",
    "url = \"https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2021-01.parquet\"\n",
    "\n",
    "if not Path(\"yellow_tripdata_2021-01.parquet\").is_file():\n",
    "    urlretrieve(url, \"yellow_tripdata_2021.parquet\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "50e7c60f",
   "metadata": {},
   "source": [
    "## Installation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2708d4a7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "%pip install jupysql --upgrade --quiet"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "e41a3624",
   "metadata": {},
   "source": [
    "## Set connection\n",
    "\n",
    "After our dataset is ready, we should set our connection.\n",
    "\n",
    "For this demonstration, we'll be using the `DuckDB` connection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "dbe40317",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Tip: You may define configurations in /Users/neelashasen/Dev/jupysql_master/jupysql/pyproject.toml or /Users/neelashasen/.jupysql/config. </span>"
      ],
      "text/plain": [
       "Tip: You may define configurations in /Users/neelashasen/Dev/jupysql_master/jupysql/pyproject.toml or /Users/neelashasen/.jupysql/config. "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "Please review our <a href='https://jupysql.ploomber.io/en/latest/api/configuration.html#loading-from-a-file'>configuration guideline</a>."
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Loading configurations from /Users/neelashasen/.jupysql/config.</span>"
      ],
      "text/plain": [
       "Loading configurations from /Users/neelashasen/.jupysql/config."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Settings changed:</span>"
      ],
      "text/plain": [
       "Settings changed:"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "\n",
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Config</th>\n",
       "            <th>value</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>feedback</td>\n",
       "            <td>True</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>autopandas</td>\n",
       "            <td>True</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "\n",
       "+------------+-------+\n",
       "|   Config   | value |\n",
       "+------------+-------+\n",
       "|  feedback  |  True |\n",
       "| autopandas |  True |\n",
       "+------------+-------+"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Connecting to &#x27;default&#x27;</span>"
      ],
      "text/plain": [
       "Connecting to 'default'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Connecting and switching to connection &#x27;duckdb://&#x27;</span>"
      ],
      "text/plain": [
       "Connecting and switching to connection 'duckdb://'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%load_ext sql\n",
    "%sql duckdb://"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "08358b2d",
   "metadata": {},
   "source": [
    "## Create the table\n",
    "\n",
    "To create the table, use the `explore` attribute and specify the name of the table that was just downloaded."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "7e6c6c7d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    <style>.sort-button {\n",
       "    background: none;\n",
       "    border: none;\n",
       "}\n",
       "\n",
       ".sort-button.selected {\n",
       "    background: #efefef;\n",
       "    border: 1px solid #767676;\n",
       "}\n",
       "\n",
       ".pages-buttons button.selected {\n",
       "    background: #efefef;\n",
       "    border: 1px solid #767676;\n",
       "    border-radius: 2px;\n",
       "}\n",
       ".pages-buttons button {\n",
       "    background: none;\n",
       "    border: none;\n",
       "    padding: 0 10px;\n",
       "}\n",
       ".jupysql-table-widget {\n",
       "    display: inline;\n",
       "}</style>\n",
       "    \n",
       "            <div id=\"tableContainer_1706166808\" class=\"table-container\"></div>\n",
       "            \n",
       "    <script>function isJupyterNotebook() {\n",
       "    return window[\"Jupyter\"];\n",
       "}\n",
       "\n",
       "function getTable(element) {\n",
       "    let table;\n",
       "    if (element) {\n",
       "        const tableContainer = element.closest(\".table-container\");\n",
       "        table = tableContainer.querySelector(\"table\");\n",
       "    } else {\n",
       "        const _isJupyterNotebook = isJupyterNotebook();\n",
       "        if (_isJupyterNotebook) {\n",
       "            table = document.querySelector(\".selected .table-container table\");\n",
       "        } else {\n",
       "            table = document.querySelector(\".jp-Cell.jp-mod-active .table-container table\");\n",
       "        }\n",
       "    }\n",
       "\n",
       "    return table;\n",
       "}\n",
       "\n",
       "function getSortDetails() {\n",
       "    let sort = undefined;\n",
       "\n",
       "    const table = getTable();\n",
       "    if (table) {\n",
       "        const column = table.getAttribute(\"sort-by-column\");\n",
       "        const order = table.getAttribute(\"sort-by-order\");\n",
       "\n",
       "        if (column && order) {\n",
       "            sort = {\n",
       "                \"column\" : column,\n",
       "                \"order\" : order\n",
       "            }\n",
       "        }\n",
       "    }\n",
       "\n",
       "    return sort;\n",
       "}\n",
       "\n",
       "function sortColumnClick(element, column, order, callback) {\n",
       "    // fetch data with sort logic\n",
       "    const table = getTable(element);\n",
       "    table.setAttribute(\"sort-by-column\", column);\n",
       "    table.setAttribute(\"sort-by-order\", order);\n",
       "    const rowsPerPage = table.getAttribute(\"rows-per-page\");\n",
       "    const currrPage = table.getAttribute(\"curr-page-idx\");\n",
       "\n",
       "    const sort = {\n",
       "        'column' : column,\n",
       "        'order' : order\n",
       "    }\n",
       "\n",
       "    const fetchParameters = {\n",
       "        rowsPerPage : parseInt(rowsPerPage),\n",
       "        page : parseInt(currrPage),\n",
       "        sort : sort,\n",
       "        table : table.getAttribute(\"table-name\")\n",
       "    }\n",
       "\n",
       "    fetchTableData(fetchParameters, callback)\n",
       "}\n",
       "\n",
       "function fetchTableData(fetchParameters, callback) {\n",
       "    \n",
       "    sendObject = {\n",
       "        'nRows' : fetchParameters.rowsPerPage,\n",
       "        'page': fetchParameters.page,\n",
       "        'table' : fetchParameters.table\n",
       "    }\n",
       "\n",
       "    if (fetchParameters.sort) {\n",
       "        sendObject.sort = fetchParameters.sort\n",
       "    }\n",
       "\n",
       "    const _isJupyterNotebook = isJupyterNotebook();\n",
       "\n",
       "\n",
       "    if (_isJupyterNotebook) {\n",
       "        // for Jupyter Notebook\n",
       "        const comm =\n",
       "        Jupyter.notebook.kernel.comm_manager.new_comm('comm_target_handle_table_widget', {})\n",
       "        comm.send(sendObject)\n",
       "        comm.on_msg(function(msg) {\n",
       "            const rows = JSON.parse(msg.content.data['rows']);\n",
       "            if (callback) {\n",
       "                callback(rows)\n",
       "            }\n",
       "        });\n",
       "    } else{\n",
       "        // for JupyterLab\n",
       "        dispatchEventToKernel(sendObject)\n",
       "\n",
       "        const controller = new AbortController();\n",
       "    \n",
       "        document.addEventListener('onTableWidgetRowsReady', (customEvent) => {\n",
       "            const rows = JSON.parse(customEvent.detail.data.rows)\n",
       "            controller.abort()\n",
       "            if (callback) {\n",
       "                callback(rows)\n",
       "            }\n",
       "        }, {signal: controller.signal})\n",
       "    }\n",
       "\n",
       "\n",
       "}\n",
       "\n",
       "\n",
       "function dispatchEventToKernel(data) {\n",
       "    let customEvent = new CustomEvent('onUpdateTableWidget', {\n",
       "    bubbles: true,\n",
       "    cancelable: true,\n",
       "    composed: false,\n",
       "    detail : {\n",
       "        data : data\n",
       "    }\n",
       "    });\n",
       "    document.body.dispatchEvent(customEvent)\n",
       "}         \n",
       "\n",
       "function handleRowsNumberOfRowsChange(e) {\n",
       "    const rowsPerPage = parseInt(e.value);\n",
       "    let table = getTable();\n",
       "    table.setAttribute('rows-per-page', rowsPerPage);\n",
       "\n",
       "    const nTotal = table.getAttribute('n-total');\n",
       "\n",
       "    const maxPages = Math.ceil(nTotal / rowsPerPage)\n",
       "    table.setAttribute('max-pages', maxPages);\n",
       "\n",
       "    const fetchParameters = {\n",
       "        rowsPerPage : rowsPerPage,\n",
       "        page : 0,\n",
       "        sort : getSortDetails(),\n",
       "        table : table.getAttribute(\"table-name\")\n",
       "    }\n",
       "\n",
       "    setTimeout(() => {\n",
       "        fetchTableData(fetchParameters, (rows) => {\n",
       "            updateTable(rows);\n",
       "        })\n",
       "    }, 100);\n",
       "}\n",
       "\n",
       "function updateTable(rows, currPage, tableToUpdate) {\n",
       "    const table = tableToUpdate || getTable();\n",
       "    const trs = table.querySelectorAll(\"tbody tr\");\n",
       "    const tbody = table.querySelector(\"tbody\");\n",
       "    tbody.innerHTML = \"\";\n",
       "\n",
       "    const _html = createTableRows(rows)\n",
       "\n",
       "    tbody.innerHTML = _html\n",
       "\n",
       "    setTimeout(() => {\n",
       "        updatePaginationBar(table, currPage || 0)\n",
       "    }, 100)\n",
       "}\n",
       "\n",
       "function createTableRows(rows) {\n",
       "    const _html = rows.map(function(row) {\n",
       "        const tds =\n",
       "        Object.keys(row).map(function(key) {\n",
       "\n",
       "            return \"<td>\" + row[key] + \"</td>\"\n",
       "        }).join(\"\") ;\n",
       "        return \"<tr>\" + tds + \"</tr>\";\n",
       "    }).join(\"\");\n",
       "\n",
       "    return _html\n",
       "}\n",
       "\n",
       "function showTablePage(page, rowsPerPage, data) {\n",
       "    const table = getTable();\n",
       "    const trs = table.querySelectorAll(\"tbody tr\");\n",
       "    const tbody = table.querySelector(\"tbody\");\n",
       "    tbody.innerHTML = \"\";\n",
       "\n",
       "    const rows = data;\n",
       "    const startIndex = page * rowsPerPage;\n",
       "    const endIndex = startIndex + rowsPerPage;\n",
       "    const _html = rows.map(row => {\n",
       "        const tds =\n",
       "        Object.keys(row).map(key => `<td>${row[key]}</td>`).join(\"\");\n",
       "        return `<tr>${tds}</tr>`;\n",
       "    }).join(\"\");\n",
       "\n",
       "    tbody.innerHTML = _html;\n",
       "\n",
       "    table.setAttribute(\"curr-page-idx\", page);\n",
       "    updatePaginationBar(table, page);\n",
       "}\n",
       "\n",
       "function nextPageClick(element) {\n",
       "    const table = getTable(element);\n",
       "    const currPageIndex = parseInt(table.getAttribute(\"curr-page-idx\"));\n",
       "    const rowsPerPage = parseInt(table.getAttribute(\"rows-per-page\"));\n",
       "    const maxPages = parseInt(table.getAttribute(\"max-pages\"));\n",
       "\n",
       "    const nextPage = currPageIndex + 1;\n",
       "    if (nextPage < maxPages) {\n",
       "        const fetchParameters = {\n",
       "            rowsPerPage : rowsPerPage,\n",
       "            page : nextPage,\n",
       "            sort : getSortDetails(),\n",
       "            table : table.getAttribute(\"table-name\")\n",
       "        }\n",
       "\n",
       "        fetchTableData(fetchParameters, (rows) => {\n",
       "            showTablePage(nextPage, rowsPerPage, rows)\n",
       "        });\n",
       "    }\n",
       "\n",
       "}\n",
       "\n",
       "function prevPageClick() {\n",
       "    const table = getTable();\n",
       "    const currPageIndex = parseInt(table.getAttribute(\"curr-page-idx\"));\n",
       "    const rowsPerPage = parseInt(table.getAttribute(\"rows-per-page\"));\n",
       "    const prevPage = currPageIndex - 1;\n",
       "    if (prevPage >= 0) {\n",
       "        const fetchParameters = {\n",
       "            rowsPerPage : rowsPerPage,\n",
       "            page : prevPage,\n",
       "            sort : getSortDetails(),\n",
       "            table : table.getAttribute(\"table-name\")\n",
       "        }\n",
       "\n",
       "        fetchTableData(fetchParameters, (rows) => {\n",
       "            showTablePage(prevPage, rowsPerPage, rows)\n",
       "        });\n",
       "    }\n",
       "}\n",
       "\n",
       "function setPageButton(table, label, navigateTo, isSelected) {\n",
       "    const rowsPerPage = parseInt(table.getAttribute(\"rows-per-page\"));\n",
       "    const selected = isSelected ? \"selected\" : \"\";\n",
       "\n",
       "    const button = `\n",
       "    <button class=\"${selected}\"\n",
       "            onclick=\"\n",
       "            fetchTableData({\n",
       "                rowsPerPage : ${rowsPerPage},\n",
       "                page : ${navigateTo},\n",
       "                sort : getSortDetails(),\n",
       "                table : getTable(this).getAttribute('table-name')\n",
       "            },\n",
       "            (rows) => {\n",
       "                showTablePage(${navigateTo}, ${rowsPerPage}, rows);\n",
       "                })\"\n",
       "    >\n",
       "        ${label}\n",
       "    </button>\n",
       "    `\n",
       "    return button;\n",
       "}\n",
       "\n",
       "function updatePaginationBar(table, currPage) {\n",
       "    const maxPages = parseInt(table.getAttribute(\"max-pages\"));\n",
       "    const maxPagesInRow = 6;\n",
       "    const rowsPerPage = parseInt(table.getAttribute(\"rows-per-page\"));\n",
       "    table.setAttribute(\"curr-page-idx\", currPage);\n",
       "\n",
       "    let buttonsArray = []\n",
       "\n",
       "    let startEllipsisAdded = false\n",
       "    let endEllipsisAdded = false\n",
       "\n",
       "    // add first\n",
       "    let selected = currPage === 0;\n",
       "    buttonsArray.push(setPageButton(table, \"1\", 0, selected));\n",
       "\n",
       "    for (i = 1; i < maxPages - 1; i++) {\n",
       "        const navigateTo = i;\n",
       "        const label = i + 1;\n",
       "        selected = currPage === i;\n",
       "        const inStartRange = currPage < maxPagesInRow;\n",
       "        const inEndRange = maxPages - 1 - currPage < maxPagesInRow;\n",
       "\n",
       "        if (inStartRange) {\n",
       "            if (i < maxPagesInRow) {\n",
       "                buttonsArray\n",
       "                .push(setPageButton(table, label, navigateTo, selected));\n",
       "            } else {\n",
       "            if (!startEllipsisAdded) {\n",
       "                buttonsArray.push(\"...\");\n",
       "                startEllipsisAdded = true;\n",
       "            }\n",
       "            }\n",
       "        } else if (inEndRange) {\n",
       "            if (maxPages - 1 - i < maxPagesInRow) {\n",
       "                buttonsArray\n",
       "                .push(setPageButton(table, label, navigateTo, selected));\n",
       "            } else {\n",
       "            if (!endEllipsisAdded) {\n",
       "                buttonsArray.push(\"...\");\n",
       "                endEllipsisAdded = true;\n",
       "            }\n",
       "            }\n",
       "        }\n",
       "\n",
       "        if (!inStartRange && !inEndRange) {\n",
       "            if (currPage === i-2) {\n",
       "                buttonsArray.push(\"...\");\n",
       "            }\n",
       "            if (\n",
       "                currPage === i - 1 ||\n",
       "                currPage === i ||\n",
       "                currPage === i + 1\n",
       "            ) {\n",
       "                buttonsArray\n",
       "                .push(setPageButton(table, label, navigateTo, selected))\n",
       "            }\n",
       "\n",
       "            if (currPage === i+2) {\n",
       "                buttonsArray.push(\"...\");\n",
       "            }\n",
       "\n",
       "        }\n",
       "    }\n",
       "\n",
       "    selected = currPage === maxPages - 1 ? \"selected\" : \"\";\n",
       "\n",
       "    buttonsArray.\n",
       "    push(setPageButton(table, maxPages, maxPages - 1, selected))\n",
       "\n",
       "    const buttonsHtml = buttonsArray.join(\"\");\n",
       "    table.parentNode\n",
       "    .querySelector(\".pages-buttons\").innerHTML = buttonsHtml;\n",
       "}\n",
       "\n",
       "function removeSelectionFromAllSortButtons() {\n",
       "    document.querySelectorAll(\".sort-button\")\n",
       "    .forEach(el => el.classList.remove(\"selected\"))\n",
       "}\n",
       "\n",
       "function initTable() {\n",
       "    // template variables we should pass\n",
       "    const initialRows = [\n",
       "    {\n",
       "        \"DOLocationID\": 43,\n",
       "        \"PULocationID\": 142,\n",
       "        \"RatecodeID\": 1.0,\n",
       "        \"VendorID\": 1,\n",
       "        \"airport_fee\": \"None\",\n",
       "        \"congestion_surcharge\": 2.5,\n",
       "        \"extra\": 3.0,\n",
       "        \"fare_amount\": 8.0,\n",
       "        \"improvement_surcharge\": 0.3,\n",
       "        \"mta_tax\": 0.5,\n",
       "        \"passenger_count\": 1.0,\n",
       "        \"payment_type\": 2,\n",
       "        \"store_and_fwd_flag\": \"N\",\n",
       "        \"tip_amount\": 0.0,\n",
       "        \"tolls_amount\": 0.0,\n",
       "        \"total_amount\": 11.8,\n",
       "        \"tpep_dropoff_datetime\": \"2021-01-01 00:36:12\",\n",
       "        \"tpep_pickup_datetime\": \"2021-01-01 00:30:10\",\n",
       "        \"trip_distance\": 2.1\n",
       "    },\n",
       "    {\n",
       "        \"DOLocationID\": 151,\n",
       "        \"PULocationID\": 238,\n",
       "        \"RatecodeID\": 1.0,\n",
       "        \"VendorID\": 1,\n",
       "        \"airport_fee\": \"None\",\n",
       "        \"congestion_surcharge\": 0.0,\n",
       "        \"extra\": 0.5,\n",
       "        \"fare_amount\": 3.0,\n",
       "        \"improvement_surcharge\": 0.3,\n",
       "        \"mta_tax\": 0.5,\n",
       "        \"passenger_count\": 1.0,\n",
       "        \"payment_type\": 2,\n",
       "        \"store_and_fwd_flag\": \"N\",\n",
       "        \"tip_amount\": 0.0,\n",
       "        \"tolls_amount\": 0.0,\n",
       "        \"total_amount\": 4.3,\n",
       "        \"tpep_dropoff_datetime\": \"2021-01-01 00:52:19\",\n",
       "        \"tpep_pickup_datetime\": \"2021-01-01 00:51:20\",\n",
       "        \"trip_distance\": 0.2\n",
       "    },\n",
       "    {\n",
       "        \"DOLocationID\": 165,\n",
       "        \"PULocationID\": 132,\n",
       "        \"RatecodeID\": 1.0,\n",
       "        \"VendorID\": 1,\n",
       "        \"airport_fee\": \"None\",\n",
       "        \"congestion_surcharge\": 0.0,\n",
       "        \"extra\": 0.5,\n",
       "        \"fare_amount\": 42.0,\n",
       "        \"improvement_surcharge\": 0.3,\n",
       "        \"mta_tax\": 0.5,\n",
       "        \"passenger_count\": 1.0,\n",
       "        \"payment_type\": 1,\n",
       "        \"store_and_fwd_flag\": \"N\",\n",
       "        \"tip_amount\": 8.65,\n",
       "        \"tolls_amount\": 0.0,\n",
       "        \"total_amount\": 51.95,\n",
       "        \"tpep_dropoff_datetime\": \"2021-01-01 01:11:06\",\n",
       "        \"tpep_pickup_datetime\": \"2021-01-01 00:43:30\",\n",
       "        \"trip_distance\": 14.7\n",
       "    },\n",
       "    {\n",
       "        \"DOLocationID\": 132,\n",
       "        \"PULocationID\": 138,\n",
       "        \"RatecodeID\": 1.0,\n",
       "        \"VendorID\": 1,\n",
       "        \"airport_fee\": \"None\",\n",
       "        \"congestion_surcharge\": 0.0,\n",
       "        \"extra\": 0.5,\n",
       "        \"fare_amount\": 29.0,\n",
       "        \"improvement_surcharge\": 0.3,\n",
       "        \"mta_tax\": 0.5,\n",
       "        \"passenger_count\": 0.0,\n",
       "        \"payment_type\": 1,\n",
       "        \"store_and_fwd_flag\": \"N\",\n",
       "        \"tip_amount\": 6.05,\n",
       "        \"tolls_amount\": 0.0,\n",
       "        \"total_amount\": 36.35,\n",
       "        \"tpep_dropoff_datetime\": \"2021-01-01 00:31:01\",\n",
       "        \"tpep_pickup_datetime\": \"2021-01-01 00:15:48\",\n",
       "        \"trip_distance\": 10.6\n",
       "    },\n",
       "    {\n",
       "        \"DOLocationID\": 33,\n",
       "        \"PULocationID\": 68,\n",
       "        \"RatecodeID\": 1.0,\n",
       "        \"VendorID\": 2,\n",
       "        \"airport_fee\": \"None\",\n",
       "        \"congestion_surcharge\": 2.5,\n",
       "        \"extra\": 0.5,\n",
       "        \"fare_amount\": 16.5,\n",
       "        \"improvement_surcharge\": 0.3,\n",
       "        \"mta_tax\": 0.5,\n",
       "        \"passenger_count\": 1.0,\n",
       "        \"payment_type\": 1,\n",
       "        \"store_and_fwd_flag\": \"N\",\n",
       "        \"tip_amount\": 4.06,\n",
       "        \"tolls_amount\": 0.0,\n",
       "        \"total_amount\": 24.36,\n",
       "        \"tpep_dropoff_datetime\": \"2021-01-01 00:48:21\",\n",
       "        \"tpep_pickup_datetime\": \"2021-01-01 00:31:49\",\n",
       "        \"trip_distance\": 4.94\n",
       "    },\n",
       "    {\n",
       "        \"DOLocationID\": 68,\n",
       "        \"PULocationID\": 224,\n",
       "        \"RatecodeID\": 1.0,\n",
       "        \"VendorID\": 1,\n",
       "        \"airport_fee\": \"None\",\n",
       "        \"congestion_surcharge\": 2.5,\n",
       "        \"extra\": 3.0,\n",
       "        \"fare_amount\": 8.0,\n",
       "        \"improvement_surcharge\": 0.3,\n",
       "        \"mta_tax\": 0.5,\n",
       "        \"passenger_count\": 1.0,\n",
       "        \"payment_type\": 1,\n",
       "        \"store_and_fwd_flag\": \"N\",\n",
       "        \"tip_amount\": 2.35,\n",
       "        \"tolls_amount\": 0.0,\n",
       "        \"total_amount\": 14.15,\n",
       "        \"tpep_dropoff_datetime\": \"2021-01-01 00:24:30\",\n",
       "        \"tpep_pickup_datetime\": \"2021-01-01 00:16:29\",\n",
       "        \"trip_distance\": 1.6\n",
       "    },\n",
       "    {\n",
       "        \"DOLocationID\": 157,\n",
       "        \"PULocationID\": 95,\n",
       "        \"RatecodeID\": 1.0,\n",
       "        \"VendorID\": 1,\n",
       "        \"airport_fee\": \"None\",\n",
       "        \"congestion_surcharge\": 0.0,\n",
       "        \"extra\": 0.5,\n",
       "        \"fare_amount\": 16.0,\n",
       "        \"improvement_surcharge\": 0.3,\n",
       "        \"mta_tax\": 0.5,\n",
       "        \"passenger_count\": 1.0,\n",
       "        \"payment_type\": 2,\n",
       "        \"store_and_fwd_flag\": \"N\",\n",
       "        \"tip_amount\": 0.0,\n",
       "        \"tolls_amount\": 0.0,\n",
       "        \"total_amount\": 17.3,\n",
       "        \"tpep_dropoff_datetime\": \"2021-01-01 00:17:28\",\n",
       "        \"tpep_pickup_datetime\": \"2021-01-01 00:00:28\",\n",
       "        \"trip_distance\": 4.1\n",
       "    },\n",
       "    {\n",
       "        \"DOLocationID\": 40,\n",
       "        \"PULocationID\": 90,\n",
       "        \"RatecodeID\": 1.0,\n",
       "        \"VendorID\": 1,\n",
       "        \"airport_fee\": \"None\",\n",
       "        \"congestion_surcharge\": 2.5,\n",
       "        \"extra\": 3.0,\n",
       "        \"fare_amount\": 18.0,\n",
       "        \"improvement_surcharge\": 0.3,\n",
       "        \"mta_tax\": 0.5,\n",
       "        \"passenger_count\": 1.0,\n",
       "        \"payment_type\": 2,\n",
       "        \"store_and_fwd_flag\": \"N\",\n",
       "        \"tip_amount\": 0.0,\n",
       "        \"tolls_amount\": 0.0,\n",
       "        \"total_amount\": 21.8,\n",
       "        \"tpep_dropoff_datetime\": \"2021-01-01 00:30:34\",\n",
       "        \"tpep_pickup_datetime\": \"2021-01-01 00:12:29\",\n",
       "        \"trip_distance\": 5.7\n",
       "    },\n",
       "    {\n",
       "        \"DOLocationID\": 129,\n",
       "        \"PULocationID\": 97,\n",
       "        \"RatecodeID\": 1.0,\n",
       "        \"VendorID\": 1,\n",
       "        \"airport_fee\": \"None\",\n",
       "        \"congestion_surcharge\": 0.0,\n",
       "        \"extra\": 0.5,\n",
       "        \"fare_amount\": 27.5,\n",
       "        \"improvement_surcharge\": 0.3,\n",
       "        \"mta_tax\": 0.5,\n",
       "        \"passenger_count\": 1.0,\n",
       "        \"payment_type\": 4,\n",
       "        \"store_and_fwd_flag\": \"N\",\n",
       "        \"tip_amount\": 0.0,\n",
       "        \"tolls_amount\": 0.0,\n",
       "        \"total_amount\": 28.8,\n",
       "        \"tpep_dropoff_datetime\": \"2021-01-01 01:00:13\",\n",
       "        \"tpep_pickup_datetime\": \"2021-01-01 00:39:16\",\n",
       "        \"trip_distance\": 9.1\n",
       "    },\n",
       "    {\n",
       "        \"DOLocationID\": 142,\n",
       "        \"PULocationID\": 263,\n",
       "        \"RatecodeID\": 1.0,\n",
       "        \"VendorID\": 1,\n",
       "        \"airport_fee\": \"None\",\n",
       "        \"congestion_surcharge\": 2.5,\n",
       "        \"extra\": 3.0,\n",
       "        \"fare_amount\": 12.0,\n",
       "        \"improvement_surcharge\": 0.3,\n",
       "        \"mta_tax\": 0.5,\n",
       "        \"passenger_count\": 2.0,\n",
       "        \"payment_type\": 1,\n",
       "        \"store_and_fwd_flag\": \"N\",\n",
       "        \"tip_amount\": 3.15,\n",
       "        \"tolls_amount\": 0.0,\n",
       "        \"total_amount\": 18.95,\n",
       "        \"tpep_dropoff_datetime\": \"2021-01-01 00:39:46\",\n",
       "        \"tpep_pickup_datetime\": \"2021-01-01 00:26:12\",\n",
       "        \"trip_distance\": 2.7\n",
       "    }\n",
       "];\n",
       "    const columns = ['VendorID', 'tpep_pickup_datetime', 'tpep_dropoff_datetime', 'passenger_count', 'trip_distance', 'RatecodeID', 'store_and_fwd_flag', 'PULocationID', 'DOLocationID', 'payment_type', 'fare_amount', 'extra', 'mta_tax', 'tip_amount', 'tolls_amount', 'improvement_surcharge', 'total_amount', 'congestion_surcharge', 'airport_fee'];\n",
       "    const rowsPerPage=10;\n",
       "    const nPages=136977;\n",
       "    const nTotal=1369769;\n",
       "    const tableName=\"yellow_tripdata_2021.parquet\";\n",
       "    const tableContainerId = \"tableContainer_1706166808\";\n",
       "    const options = [10, 25, 50, 100];\n",
       "    options_html =\n",
       "    options.map(option => `<option value=${option}>${option}</option>`);\n",
       "\n",
       "\n",
       "    let ths_ = columns.map(col => `<th>${col}</th>`).join(\"\");\n",
       "\n",
       "    let table = `\n",
       "    <div>\n",
       "        <span style=\"margin-right: 5px\">Show</span>\n",
       "        <select\n",
       "        onchange=\"handleRowsNumberOfRowsChange(this)\">\n",
       "            ${options_html}\n",
       "        </select>\n",
       "        <span style=\"margin-left: 5px\">entries</span>\n",
       "    </div>\n",
       "\n",
       "    <table\n",
       "        class=\"jupysql-table-widget\"\n",
       "        style='width:100%'\n",
       "        curr-page-idx=0\n",
       "        rows-per-page=${rowsPerPage}\n",
       "        max-pages = ${nPages}\n",
       "        n-total=${nTotal}\n",
       "        table-name=${tableName}\n",
       "    >\n",
       "        <thead>\n",
       "            <tr>\n",
       "                ${ths_}\n",
       "            </tr>\n",
       "        </thead>\n",
       "\n",
       "        <tbody>\n",
       "        </tbody>\n",
       "    </table>\n",
       "\n",
       "\n",
       "    <div style=\"padding-bottom: 20px;\">\n",
       "        <button onclick=\"prevPageClick(this)\">Previous</button>\n",
       "        <div\n",
       "            id = \"pagesButtons\"\n",
       "            class = \"pages-buttons\"\n",
       "            style = \"display: inline-flex\">\n",
       "        </div>\n",
       "        <button onclick=\"nextPageClick(this)\">Next</button>\n",
       "    </div>\n",
       "    `\n",
       "\n",
       "    let tableContainer = document.querySelector(`#${tableContainerId}`);\n",
       "\n",
       "    tableContainer.innerHTML = table\n",
       "\n",
       "    if (initialRows) {\n",
       "        initializeTableRows(tableContainer, rowsPerPage, initialRows)\n",
       "\n",
       "    } else {\n",
       "        setTimeout(() => {\n",
       "            const fetchParameters = {\n",
       "                rowsPerPage : rowsPerPage,\n",
       "                page : 0,\n",
       "                sort : getSortDetails(),\n",
       "                table : tableName\n",
       "            }\n",
       "\n",
       "            fetchTableData(fetchParameters, (rows) => {\n",
       "                initializeTableRows(tableContainer, rowsPerPage, rows)\n",
       "            })\n",
       "        }, 100);\n",
       "    }\n",
       "    \n",
       "}\n",
       "\n",
       "function initializeTableRows(tableContainer, rowsPerPage, rows) {\n",
       "    updateTable(rows, 0,\n",
       "        tableContainer.querySelector(\"table\"));\n",
       "    // update ths_ to make sure order columns\n",
       "    // are matching the data\n",
       "    if (rows.length > 0) {\n",
       "        let row = rows[0];\n",
       "        let ths_ =\n",
       "        Object.keys(row).map(col =>\n",
       "        `<th>\n",
       "            <div style=\"display: inline-flex; height: 40px\">\n",
       "                <span style=\"line-height: 40px\">${col}</span>\n",
       "                <span style=\"width: 40px;\">\n",
       "                    <button\n",
       "                        class = \"sort-button\"\n",
       "                        onclick='sortColumnClick(this,\n",
       "                        \"${col}\", \"ASC\",\n",
       "                        (rows) => {\n",
       "                            const table = getTable(this);\n",
       "                            const currPage =\n",
       "                            parseInt(table.getAttribute(\"curr-page-idx\"));\n",
       "                            updateTable(rows, currPage);\n",
       "                            removeSelectionFromAllSortButtons()\n",
       "                            this.className += \" selected\"\n",
       "                            }\n",
       "                        )'\n",
       "                        title=\"Sort\"\n",
       "                        >▴\n",
       "                    </button>\n",
       "                    <button\n",
       "                        class = \"sort-button\"\n",
       "                        onclick='sortColumnClick(this,\n",
       "                        \"${col}\", \"DESC\",\n",
       "                        (rows) => {\n",
       "                            const table = getTable(this);\n",
       "                            const currPage = parseInt(\n",
       "                                table.getAttribute(\"curr-page-idx\"));\n",
       "                            updateTable(rows, currPage);\n",
       "                            removeSelectionFromAllSortButtons()\n",
       "                            this.className += \" selected\"\n",
       "                            }\n",
       "                        )'\n",
       "                        title=\"Sort\"\n",
       "                        >▾\n",
       "                    </button>\n",
       "                </span>\n",
       "            </div>\n",
       "\n",
       "            </th>`).join(\"\");\n",
       "        let thead = tableContainer.querySelector(\"thead\")\n",
       "        thead.innerHTML = ths_\n",
       "    }\n",
       "}\n",
       "\n",
       "initTable()</script>\n",
       "    "
      ],
      "text/plain": [
       "<sql.widgets.table_widget.table_widget.TableWidget at 0x13c6736d0>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sqlcmd explore --table \"yellow_tripdata_2021.parquet\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c008e2e-3a38-47ef-9073-3b0379a5b13e",
   "metadata": {},
   "source": [
    "## Parametrizing arguments\n",
    "\n",
    "JupySQL supports variable expansion of arguments in the form of `{{variable}}`. This allows the user to specify arguments with placeholders that can be replaced by variables dynamically."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "8c603c30-261f-4beb-863b-493d9d441625",
   "metadata": {},
   "outputs": [],
   "source": [
    "table_name = \"yellow_tripdata_2021.parquet\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "1d2768ba-d2ad-4e09-842e-21a06609e94d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    <style>.sort-button {\n",
       "    background: none;\n",
       "    border: none;\n",
       "}\n",
       "\n",
       ".sort-button.selected {\n",
       "    background: #efefef;\n",
       "    border: 1px solid #767676;\n",
       "}\n",
       "\n",
       ".pages-buttons button.selected {\n",
       "    background: #efefef;\n",
       "    border: 1px solid #767676;\n",
       "    border-radius: 2px;\n",
       "}\n",
       ".pages-buttons button {\n",
       "    background: none;\n",
       "    border: none;\n",
       "    padding: 0 10px;\n",
       "}\n",
       ".jupysql-table-widget {\n",
       "    display: inline;\n",
       "}</style>\n",
       "    \n",
       "            <div id=\"tableContainer_1706166994\" class=\"table-container\"></div>\n",
       "            \n",
       "    <script>function isJupyterNotebook() {\n",
       "    return window[\"Jupyter\"];\n",
       "}\n",
       "\n",
       "function getTable(element) {\n",
       "    let table;\n",
       "    if (element) {\n",
       "        const tableContainer = element.closest(\".table-container\");\n",
       "        table = tableContainer.querySelector(\"table\");\n",
       "    } else {\n",
       "        const _isJupyterNotebook = isJupyterNotebook();\n",
       "        if (_isJupyterNotebook) {\n",
       "            table = document.querySelector(\".selected .table-container table\");\n",
       "        } else {\n",
       "            table = document.querySelector(\".jp-Cell.jp-mod-active .table-container table\");\n",
       "        }\n",
       "    }\n",
       "\n",
       "    return table;\n",
       "}\n",
       "\n",
       "function getSortDetails() {\n",
       "    let sort = undefined;\n",
       "\n",
       "    const table = getTable();\n",
       "    if (table) {\n",
       "        const column = table.getAttribute(\"sort-by-column\");\n",
       "        const order = table.getAttribute(\"sort-by-order\");\n",
       "\n",
       "        if (column && order) {\n",
       "            sort = {\n",
       "                \"column\" : column,\n",
       "                \"order\" : order\n",
       "            }\n",
       "        }\n",
       "    }\n",
       "\n",
       "    return sort;\n",
       "}\n",
       "\n",
       "function sortColumnClick(element, column, order, callback) {\n",
       "    // fetch data with sort logic\n",
       "    const table = getTable(element);\n",
       "    table.setAttribute(\"sort-by-column\", column);\n",
       "    table.setAttribute(\"sort-by-order\", order);\n",
       "    const rowsPerPage = table.getAttribute(\"rows-per-page\");\n",
       "    const currrPage = table.getAttribute(\"curr-page-idx\");\n",
       "\n",
       "    const sort = {\n",
       "        'column' : column,\n",
       "        'order' : order\n",
       "    }\n",
       "\n",
       "    const fetchParameters = {\n",
       "        rowsPerPage : parseInt(rowsPerPage),\n",
       "        page : parseInt(currrPage),\n",
       "        sort : sort,\n",
       "        table : table.getAttribute(\"table-name\")\n",
       "    }\n",
       "\n",
       "    fetchTableData(fetchParameters, callback)\n",
       "}\n",
       "\n",
       "function fetchTableData(fetchParameters, callback) {\n",
       "    \n",
       "    sendObject = {\n",
       "        'nRows' : fetchParameters.rowsPerPage,\n",
       "        'page': fetchParameters.page,\n",
       "        'table' : fetchParameters.table\n",
       "    }\n",
       "\n",
       "    if (fetchParameters.sort) {\n",
       "        sendObject.sort = fetchParameters.sort\n",
       "    }\n",
       "\n",
       "    const _isJupyterNotebook = isJupyterNotebook();\n",
       "\n",
       "\n",
       "    if (_isJupyterNotebook) {\n",
       "        // for Jupyter Notebook\n",
       "        const comm =\n",
       "        Jupyter.notebook.kernel.comm_manager.new_comm('comm_target_handle_table_widget', {})\n",
       "        comm.send(sendObject)\n",
       "        comm.on_msg(function(msg) {\n",
       "            const rows = JSON.parse(msg.content.data['rows']);\n",
       "            if (callback) {\n",
       "                callback(rows)\n",
       "            }\n",
       "        });\n",
       "    } else{\n",
       "        // for JupyterLab\n",
       "        dispatchEventToKernel(sendObject)\n",
       "\n",
       "        const controller = new AbortController();\n",
       "    \n",
       "        document.addEventListener('onTableWidgetRowsReady', (customEvent) => {\n",
       "            const rows = JSON.parse(customEvent.detail.data.rows)\n",
       "            controller.abort()\n",
       "            if (callback) {\n",
       "                callback(rows)\n",
       "            }\n",
       "        }, {signal: controller.signal})\n",
       "    }\n",
       "\n",
       "\n",
       "}\n",
       "\n",
       "\n",
       "function dispatchEventToKernel(data) {\n",
       "    let customEvent = new CustomEvent('onUpdateTableWidget', {\n",
       "    bubbles: true,\n",
       "    cancelable: true,\n",
       "    composed: false,\n",
       "    detail : {\n",
       "        data : data\n",
       "    }\n",
       "    });\n",
       "    document.body.dispatchEvent(customEvent)\n",
       "}         \n",
       "\n",
       "function handleRowsNumberOfRowsChange(e) {\n",
       "    const rowsPerPage = parseInt(e.value);\n",
       "    let table = getTable();\n",
       "    table.setAttribute('rows-per-page', rowsPerPage);\n",
       "\n",
       "    const nTotal = table.getAttribute('n-total');\n",
       "\n",
       "    const maxPages = Math.ceil(nTotal / rowsPerPage)\n",
       "    table.setAttribute('max-pages', maxPages);\n",
       "\n",
       "    const fetchParameters = {\n",
       "        rowsPerPage : rowsPerPage,\n",
       "        page : 0,\n",
       "        sort : getSortDetails(),\n",
       "        table : table.getAttribute(\"table-name\")\n",
       "    }\n",
       "\n",
       "    setTimeout(() => {\n",
       "        fetchTableData(fetchParameters, (rows) => {\n",
       "            updateTable(rows);\n",
       "        })\n",
       "    }, 100);\n",
       "}\n",
       "\n",
       "function updateTable(rows, currPage, tableToUpdate) {\n",
       "    const table = tableToUpdate || getTable();\n",
       "    const trs = table.querySelectorAll(\"tbody tr\");\n",
       "    const tbody = table.querySelector(\"tbody\");\n",
       "    tbody.innerHTML = \"\";\n",
       "\n",
       "    const _html = createTableRows(rows)\n",
       "\n",
       "    tbody.innerHTML = _html\n",
       "\n",
       "    setTimeout(() => {\n",
       "        updatePaginationBar(table, currPage || 0)\n",
       "    }, 100)\n",
       "}\n",
       "\n",
       "function createTableRows(rows) {\n",
       "    const _html = rows.map(function(row) {\n",
       "        const tds =\n",
       "        Object.keys(row).map(function(key) {\n",
       "\n",
       "            return \"<td>\" + row[key] + \"</td>\"\n",
       "        }).join(\"\") ;\n",
       "        return \"<tr>\" + tds + \"</tr>\";\n",
       "    }).join(\"\");\n",
       "\n",
       "    return _html\n",
       "}\n",
       "\n",
       "function showTablePage(page, rowsPerPage, data) {\n",
       "    const table = getTable();\n",
       "    const trs = table.querySelectorAll(\"tbody tr\");\n",
       "    const tbody = table.querySelector(\"tbody\");\n",
       "    tbody.innerHTML = \"\";\n",
       "\n",
       "    const rows = data;\n",
       "    const startIndex = page * rowsPerPage;\n",
       "    const endIndex = startIndex + rowsPerPage;\n",
       "    const _html = rows.map(row => {\n",
       "        const tds =\n",
       "        Object.keys(row).map(key => `<td>${row[key]}</td>`).join(\"\");\n",
       "        return `<tr>${tds}</tr>`;\n",
       "    }).join(\"\");\n",
       "\n",
       "    tbody.innerHTML = _html;\n",
       "\n",
       "    table.setAttribute(\"curr-page-idx\", page);\n",
       "    updatePaginationBar(table, page);\n",
       "}\n",
       "\n",
       "function nextPageClick(element) {\n",
       "    const table = getTable(element);\n",
       "    const currPageIndex = parseInt(table.getAttribute(\"curr-page-idx\"));\n",
       "    const rowsPerPage = parseInt(table.getAttribute(\"rows-per-page\"));\n",
       "    const maxPages = parseInt(table.getAttribute(\"max-pages\"));\n",
       "\n",
       "    const nextPage = currPageIndex + 1;\n",
       "    if (nextPage < maxPages) {\n",
       "        const fetchParameters = {\n",
       "            rowsPerPage : rowsPerPage,\n",
       "            page : nextPage,\n",
       "            sort : getSortDetails(),\n",
       "            table : table.getAttribute(\"table-name\")\n",
       "        }\n",
       "\n",
       "        fetchTableData(fetchParameters, (rows) => {\n",
       "            showTablePage(nextPage, rowsPerPage, rows)\n",
       "        });\n",
       "    }\n",
       "\n",
       "}\n",
       "\n",
       "function prevPageClick() {\n",
       "    const table = getTable();\n",
       "    const currPageIndex = parseInt(table.getAttribute(\"curr-page-idx\"));\n",
       "    const rowsPerPage = parseInt(table.getAttribute(\"rows-per-page\"));\n",
       "    const prevPage = currPageIndex - 1;\n",
       "    if (prevPage >= 0) {\n",
       "        const fetchParameters = {\n",
       "            rowsPerPage : rowsPerPage,\n",
       "            page : prevPage,\n",
       "            sort : getSortDetails(),\n",
       "            table : table.getAttribute(\"table-name\")\n",
       "        }\n",
       "\n",
       "        fetchTableData(fetchParameters, (rows) => {\n",
       "            showTablePage(prevPage, rowsPerPage, rows)\n",
       "        });\n",
       "    }\n",
       "}\n",
       "\n",
       "function setPageButton(table, label, navigateTo, isSelected) {\n",
       "    const rowsPerPage = parseInt(table.getAttribute(\"rows-per-page\"));\n",
       "    const selected = isSelected ? \"selected\" : \"\";\n",
       "\n",
       "    const button = `\n",
       "    <button class=\"${selected}\"\n",
       "            onclick=\"\n",
       "            fetchTableData({\n",
       "                rowsPerPage : ${rowsPerPage},\n",
       "                page : ${navigateTo},\n",
       "                sort : getSortDetails(),\n",
       "                table : getTable(this).getAttribute('table-name')\n",
       "            },\n",
       "            (rows) => {\n",
       "                showTablePage(${navigateTo}, ${rowsPerPage}, rows);\n",
       "                })\"\n",
       "    >\n",
       "        ${label}\n",
       "    </button>\n",
       "    `\n",
       "    return button;\n",
       "}\n",
       "\n",
       "function updatePaginationBar(table, currPage) {\n",
       "    const maxPages = parseInt(table.getAttribute(\"max-pages\"));\n",
       "    const maxPagesInRow = 6;\n",
       "    const rowsPerPage = parseInt(table.getAttribute(\"rows-per-page\"));\n",
       "    table.setAttribute(\"curr-page-idx\", currPage);\n",
       "\n",
       "    let buttonsArray = []\n",
       "\n",
       "    let startEllipsisAdded = false\n",
       "    let endEllipsisAdded = false\n",
       "\n",
       "    // add first\n",
       "    let selected = currPage === 0;\n",
       "    buttonsArray.push(setPageButton(table, \"1\", 0, selected));\n",
       "\n",
       "    for (i = 1; i < maxPages - 1; i++) {\n",
       "        const navigateTo = i;\n",
       "        const label = i + 1;\n",
       "        selected = currPage === i;\n",
       "        const inStartRange = currPage < maxPagesInRow;\n",
       "        const inEndRange = maxPages - 1 - currPage < maxPagesInRow;\n",
       "\n",
       "        if (inStartRange) {\n",
       "            if (i < maxPagesInRow) {\n",
       "                buttonsArray\n",
       "                .push(setPageButton(table, label, navigateTo, selected));\n",
       "            } else {\n",
       "            if (!startEllipsisAdded) {\n",
       "                buttonsArray.push(\"...\");\n",
       "                startEllipsisAdded = true;\n",
       "            }\n",
       "            }\n",
       "        } else if (inEndRange) {\n",
       "            if (maxPages - 1 - i < maxPagesInRow) {\n",
       "                buttonsArray\n",
       "                .push(setPageButton(table, label, navigateTo, selected));\n",
       "            } else {\n",
       "            if (!endEllipsisAdded) {\n",
       "                buttonsArray.push(\"...\");\n",
       "                endEllipsisAdded = true;\n",
       "            }\n",
       "            }\n",
       "        }\n",
       "\n",
       "        if (!inStartRange && !inEndRange) {\n",
       "            if (currPage === i-2) {\n",
       "                buttonsArray.push(\"...\");\n",
       "            }\n",
       "            if (\n",
       "                currPage === i - 1 ||\n",
       "                currPage === i ||\n",
       "                currPage === i + 1\n",
       "            ) {\n",
       "                buttonsArray\n",
       "                .push(setPageButton(table, label, navigateTo, selected))\n",
       "            }\n",
       "\n",
       "            if (currPage === i+2) {\n",
       "                buttonsArray.push(\"...\");\n",
       "            }\n",
       "\n",
       "        }\n",
       "    }\n",
       "\n",
       "    selected = currPage === maxPages - 1 ? \"selected\" : \"\";\n",
       "\n",
       "    buttonsArray.\n",
       "    push(setPageButton(table, maxPages, maxPages - 1, selected))\n",
       "\n",
       "    const buttonsHtml = buttonsArray.join(\"\");\n",
       "    table.parentNode\n",
       "    .querySelector(\".pages-buttons\").innerHTML = buttonsHtml;\n",
       "}\n",
       "\n",
       "function removeSelectionFromAllSortButtons() {\n",
       "    document.querySelectorAll(\".sort-button\")\n",
       "    .forEach(el => el.classList.remove(\"selected\"))\n",
       "}\n",
       "\n",
       "function initTable() {\n",
       "    // template variables we should pass\n",
       "    const initialRows = [\n",
       "    {\n",
       "        \"DOLocationID\": 43,\n",
       "        \"PULocationID\": 142,\n",
       "        \"RatecodeID\": 1.0,\n",
       "        \"VendorID\": 1,\n",
       "        \"airport_fee\": \"None\",\n",
       "        \"congestion_surcharge\": 2.5,\n",
       "        \"extra\": 3.0,\n",
       "        \"fare_amount\": 8.0,\n",
       "        \"improvement_surcharge\": 0.3,\n",
       "        \"mta_tax\": 0.5,\n",
       "        \"passenger_count\": 1.0,\n",
       "        \"payment_type\": 2,\n",
       "        \"store_and_fwd_flag\": \"N\",\n",
       "        \"tip_amount\": 0.0,\n",
       "        \"tolls_amount\": 0.0,\n",
       "        \"total_amount\": 11.8,\n",
       "        \"tpep_dropoff_datetime\": \"2021-01-01 00:36:12\",\n",
       "        \"tpep_pickup_datetime\": \"2021-01-01 00:30:10\",\n",
       "        \"trip_distance\": 2.1\n",
       "    },\n",
       "    {\n",
       "        \"DOLocationID\": 151,\n",
       "        \"PULocationID\": 238,\n",
       "        \"RatecodeID\": 1.0,\n",
       "        \"VendorID\": 1,\n",
       "        \"airport_fee\": \"None\",\n",
       "        \"congestion_surcharge\": 0.0,\n",
       "        \"extra\": 0.5,\n",
       "        \"fare_amount\": 3.0,\n",
       "        \"improvement_surcharge\": 0.3,\n",
       "        \"mta_tax\": 0.5,\n",
       "        \"passenger_count\": 1.0,\n",
       "        \"payment_type\": 2,\n",
       "        \"store_and_fwd_flag\": \"N\",\n",
       "        \"tip_amount\": 0.0,\n",
       "        \"tolls_amount\": 0.0,\n",
       "        \"total_amount\": 4.3,\n",
       "        \"tpep_dropoff_datetime\": \"2021-01-01 00:52:19\",\n",
       "        \"tpep_pickup_datetime\": \"2021-01-01 00:51:20\",\n",
       "        \"trip_distance\": 0.2\n",
       "    },\n",
       "    {\n",
       "        \"DOLocationID\": 165,\n",
       "        \"PULocationID\": 132,\n",
       "        \"RatecodeID\": 1.0,\n",
       "        \"VendorID\": 1,\n",
       "        \"airport_fee\": \"None\",\n",
       "        \"congestion_surcharge\": 0.0,\n",
       "        \"extra\": 0.5,\n",
       "        \"fare_amount\": 42.0,\n",
       "        \"improvement_surcharge\": 0.3,\n",
       "        \"mta_tax\": 0.5,\n",
       "        \"passenger_count\": 1.0,\n",
       "        \"payment_type\": 1,\n",
       "        \"store_and_fwd_flag\": \"N\",\n",
       "        \"tip_amount\": 8.65,\n",
       "        \"tolls_amount\": 0.0,\n",
       "        \"total_amount\": 51.95,\n",
       "        \"tpep_dropoff_datetime\": \"2021-01-01 01:11:06\",\n",
       "        \"tpep_pickup_datetime\": \"2021-01-01 00:43:30\",\n",
       "        \"trip_distance\": 14.7\n",
       "    },\n",
       "    {\n",
       "        \"DOLocationID\": 132,\n",
       "        \"PULocationID\": 138,\n",
       "        \"RatecodeID\": 1.0,\n",
       "        \"VendorID\": 1,\n",
       "        \"airport_fee\": \"None\",\n",
       "        \"congestion_surcharge\": 0.0,\n",
       "        \"extra\": 0.5,\n",
       "        \"fare_amount\": 29.0,\n",
       "        \"improvement_surcharge\": 0.3,\n",
       "        \"mta_tax\": 0.5,\n",
       "        \"passenger_count\": 0.0,\n",
       "        \"payment_type\": 1,\n",
       "        \"store_and_fwd_flag\": \"N\",\n",
       "        \"tip_amount\": 6.05,\n",
       "        \"tolls_amount\": 0.0,\n",
       "        \"total_amount\": 36.35,\n",
       "        \"tpep_dropoff_datetime\": \"2021-01-01 00:31:01\",\n",
       "        \"tpep_pickup_datetime\": \"2021-01-01 00:15:48\",\n",
       "        \"trip_distance\": 10.6\n",
       "    },\n",
       "    {\n",
       "        \"DOLocationID\": 33,\n",
       "        \"PULocationID\": 68,\n",
       "        \"RatecodeID\": 1.0,\n",
       "        \"VendorID\": 2,\n",
       "        \"airport_fee\": \"None\",\n",
       "        \"congestion_surcharge\": 2.5,\n",
       "        \"extra\": 0.5,\n",
       "        \"fare_amount\": 16.5,\n",
       "        \"improvement_surcharge\": 0.3,\n",
       "        \"mta_tax\": 0.5,\n",
       "        \"passenger_count\": 1.0,\n",
       "        \"payment_type\": 1,\n",
       "        \"store_and_fwd_flag\": \"N\",\n",
       "        \"tip_amount\": 4.06,\n",
       "        \"tolls_amount\": 0.0,\n",
       "        \"total_amount\": 24.36,\n",
       "        \"tpep_dropoff_datetime\": \"2021-01-01 00:48:21\",\n",
       "        \"tpep_pickup_datetime\": \"2021-01-01 00:31:49\",\n",
       "        \"trip_distance\": 4.94\n",
       "    },\n",
       "    {\n",
       "        \"DOLocationID\": 68,\n",
       "        \"PULocationID\": 224,\n",
       "        \"RatecodeID\": 1.0,\n",
       "        \"VendorID\": 1,\n",
       "        \"airport_fee\": \"None\",\n",
       "        \"congestion_surcharge\": 2.5,\n",
       "        \"extra\": 3.0,\n",
       "        \"fare_amount\": 8.0,\n",
       "        \"improvement_surcharge\": 0.3,\n",
       "        \"mta_tax\": 0.5,\n",
       "        \"passenger_count\": 1.0,\n",
       "        \"payment_type\": 1,\n",
       "        \"store_and_fwd_flag\": \"N\",\n",
       "        \"tip_amount\": 2.35,\n",
       "        \"tolls_amount\": 0.0,\n",
       "        \"total_amount\": 14.15,\n",
       "        \"tpep_dropoff_datetime\": \"2021-01-01 00:24:30\",\n",
       "        \"tpep_pickup_datetime\": \"2021-01-01 00:16:29\",\n",
       "        \"trip_distance\": 1.6\n",
       "    },\n",
       "    {\n",
       "        \"DOLocationID\": 157,\n",
       "        \"PULocationID\": 95,\n",
       "        \"RatecodeID\": 1.0,\n",
       "        \"VendorID\": 1,\n",
       "        \"airport_fee\": \"None\",\n",
       "        \"congestion_surcharge\": 0.0,\n",
       "        \"extra\": 0.5,\n",
       "        \"fare_amount\": 16.0,\n",
       "        \"improvement_surcharge\": 0.3,\n",
       "        \"mta_tax\": 0.5,\n",
       "        \"passenger_count\": 1.0,\n",
       "        \"payment_type\": 2,\n",
       "        \"store_and_fwd_flag\": \"N\",\n",
       "        \"tip_amount\": 0.0,\n",
       "        \"tolls_amount\": 0.0,\n",
       "        \"total_amount\": 17.3,\n",
       "        \"tpep_dropoff_datetime\": \"2021-01-01 00:17:28\",\n",
       "        \"tpep_pickup_datetime\": \"2021-01-01 00:00:28\",\n",
       "        \"trip_distance\": 4.1\n",
       "    },\n",
       "    {\n",
       "        \"DOLocationID\": 40,\n",
       "        \"PULocationID\": 90,\n",
       "        \"RatecodeID\": 1.0,\n",
       "        \"VendorID\": 1,\n",
       "        \"airport_fee\": \"None\",\n",
       "        \"congestion_surcharge\": 2.5,\n",
       "        \"extra\": 3.0,\n",
       "        \"fare_amount\": 18.0,\n",
       "        \"improvement_surcharge\": 0.3,\n",
       "        \"mta_tax\": 0.5,\n",
       "        \"passenger_count\": 1.0,\n",
       "        \"payment_type\": 2,\n",
       "        \"store_and_fwd_flag\": \"N\",\n",
       "        \"tip_amount\": 0.0,\n",
       "        \"tolls_amount\": 0.0,\n",
       "        \"total_amount\": 21.8,\n",
       "        \"tpep_dropoff_datetime\": \"2021-01-01 00:30:34\",\n",
       "        \"tpep_pickup_datetime\": \"2021-01-01 00:12:29\",\n",
       "        \"trip_distance\": 5.7\n",
       "    },\n",
       "    {\n",
       "        \"DOLocationID\": 129,\n",
       "        \"PULocationID\": 97,\n",
       "        \"RatecodeID\": 1.0,\n",
       "        \"VendorID\": 1,\n",
       "        \"airport_fee\": \"None\",\n",
       "        \"congestion_surcharge\": 0.0,\n",
       "        \"extra\": 0.5,\n",
       "        \"fare_amount\": 27.5,\n",
       "        \"improvement_surcharge\": 0.3,\n",
       "        \"mta_tax\": 0.5,\n",
       "        \"passenger_count\": 1.0,\n",
       "        \"payment_type\": 4,\n",
       "        \"store_and_fwd_flag\": \"N\",\n",
       "        \"tip_amount\": 0.0,\n",
       "        \"tolls_amount\": 0.0,\n",
       "        \"total_amount\": 28.8,\n",
       "        \"tpep_dropoff_datetime\": \"2021-01-01 01:00:13\",\n",
       "        \"tpep_pickup_datetime\": \"2021-01-01 00:39:16\",\n",
       "        \"trip_distance\": 9.1\n",
       "    },\n",
       "    {\n",
       "        \"DOLocationID\": 142,\n",
       "        \"PULocationID\": 263,\n",
       "        \"RatecodeID\": 1.0,\n",
       "        \"VendorID\": 1,\n",
       "        \"airport_fee\": \"None\",\n",
       "        \"congestion_surcharge\": 2.5,\n",
       "        \"extra\": 3.0,\n",
       "        \"fare_amount\": 12.0,\n",
       "        \"improvement_surcharge\": 0.3,\n",
       "        \"mta_tax\": 0.5,\n",
       "        \"passenger_count\": 2.0,\n",
       "        \"payment_type\": 1,\n",
       "        \"store_and_fwd_flag\": \"N\",\n",
       "        \"tip_amount\": 3.15,\n",
       "        \"tolls_amount\": 0.0,\n",
       "        \"total_amount\": 18.95,\n",
       "        \"tpep_dropoff_datetime\": \"2021-01-01 00:39:46\",\n",
       "        \"tpep_pickup_datetime\": \"2021-01-01 00:26:12\",\n",
       "        \"trip_distance\": 2.7\n",
       "    }\n",
       "];\n",
       "    const columns = ['VendorID', 'tpep_pickup_datetime', 'tpep_dropoff_datetime', 'passenger_count', 'trip_distance', 'RatecodeID', 'store_and_fwd_flag', 'PULocationID', 'DOLocationID', 'payment_type', 'fare_amount', 'extra', 'mta_tax', 'tip_amount', 'tolls_amount', 'improvement_surcharge', 'total_amount', 'congestion_surcharge', 'airport_fee'];\n",
       "    const rowsPerPage=10;\n",
       "    const nPages=136977;\n",
       "    const nTotal=1369769;\n",
       "    const tableName=\"yellow_tripdata_2021.parquet\";\n",
       "    const tableContainerId = \"tableContainer_1706166994\";\n",
       "    const options = [10, 25, 50, 100];\n",
       "    options_html =\n",
       "    options.map(option => `<option value=${option}>${option}</option>`);\n",
       "\n",
       "\n",
       "    let ths_ = columns.map(col => `<th>${col}</th>`).join(\"\");\n",
       "\n",
       "    let table = `\n",
       "    <div>\n",
       "        <span style=\"margin-right: 5px\">Show</span>\n",
       "        <select\n",
       "        onchange=\"handleRowsNumberOfRowsChange(this)\">\n",
       "            ${options_html}\n",
       "        </select>\n",
       "        <span style=\"margin-left: 5px\">entries</span>\n",
       "    </div>\n",
       "\n",
       "    <table\n",
       "        class=\"jupysql-table-widget\"\n",
       "        style='width:100%'\n",
       "        curr-page-idx=0\n",
       "        rows-per-page=${rowsPerPage}\n",
       "        max-pages = ${nPages}\n",
       "        n-total=${nTotal}\n",
       "        table-name=${tableName}\n",
       "    >\n",
       "        <thead>\n",
       "            <tr>\n",
       "                ${ths_}\n",
       "            </tr>\n",
       "        </thead>\n",
       "\n",
       "        <tbody>\n",
       "        </tbody>\n",
       "    </table>\n",
       "\n",
       "\n",
       "    <div style=\"padding-bottom: 20px;\">\n",
       "        <button onclick=\"prevPageClick(this)\">Previous</button>\n",
       "        <div\n",
       "            id = \"pagesButtons\"\n",
       "            class = \"pages-buttons\"\n",
       "            style = \"display: inline-flex\">\n",
       "        </div>\n",
       "        <button onclick=\"nextPageClick(this)\">Next</button>\n",
       "    </div>\n",
       "    `\n",
       "\n",
       "    let tableContainer = document.querySelector(`#${tableContainerId}`);\n",
       "\n",
       "    tableContainer.innerHTML = table\n",
       "\n",
       "    if (initialRows) {\n",
       "        initializeTableRows(tableContainer, rowsPerPage, initialRows)\n",
       "\n",
       "    } else {\n",
       "        setTimeout(() => {\n",
       "            const fetchParameters = {\n",
       "                rowsPerPage : rowsPerPage,\n",
       "                page : 0,\n",
       "                sort : getSortDetails(),\n",
       "                table : tableName\n",
       "            }\n",
       "\n",
       "            fetchTableData(fetchParameters, (rows) => {\n",
       "                initializeTableRows(tableContainer, rowsPerPage, rows)\n",
       "            })\n",
       "        }, 100);\n",
       "    }\n",
       "    \n",
       "}\n",
       "\n",
       "function initializeTableRows(tableContainer, rowsPerPage, rows) {\n",
       "    updateTable(rows, 0,\n",
       "        tableContainer.querySelector(\"table\"));\n",
       "    // update ths_ to make sure order columns\n",
       "    // are matching the data\n",
       "    if (rows.length > 0) {\n",
       "        let row = rows[0];\n",
       "        let ths_ =\n",
       "        Object.keys(row).map(col =>\n",
       "        `<th>\n",
       "            <div style=\"display: inline-flex; height: 40px\">\n",
       "                <span style=\"line-height: 40px\">${col}</span>\n",
       "                <span style=\"width: 40px;\">\n",
       "                    <button\n",
       "                        class = \"sort-button\"\n",
       "                        onclick='sortColumnClick(this,\n",
       "                        \"${col}\", \"ASC\",\n",
       "                        (rows) => {\n",
       "                            const table = getTable(this);\n",
       "                            const currPage =\n",
       "                            parseInt(table.getAttribute(\"curr-page-idx\"));\n",
       "                            updateTable(rows, currPage);\n",
       "                            removeSelectionFromAllSortButtons()\n",
       "                            this.className += \" selected\"\n",
       "                            }\n",
       "                        )'\n",
       "                        title=\"Sort\"\n",
       "                        >▴\n",
       "                    </button>\n",
       "                    <button\n",
       "                        class = \"sort-button\"\n",
       "                        onclick='sortColumnClick(this,\n",
       "                        \"${col}\", \"DESC\",\n",
       "                        (rows) => {\n",
       "                            const table = getTable(this);\n",
       "                            const currPage = parseInt(\n",
       "                                table.getAttribute(\"curr-page-idx\"));\n",
       "                            updateTable(rows, currPage);\n",
       "                            removeSelectionFromAllSortButtons()\n",
       "                            this.className += \" selected\"\n",
       "                            }\n",
       "                        )'\n",
       "                        title=\"Sort\"\n",
       "                        >▾\n",
       "                    </button>\n",
       "                </span>\n",
       "            </div>\n",
       "\n",
       "            </th>`).join(\"\");\n",
       "        let thead = tableContainer.querySelector(\"thead\")\n",
       "        thead.innerHTML = ths_\n",
       "    }\n",
       "}\n",
       "\n",
       "initTable()</script>\n",
       "    "
      ],
      "text/plain": [
       "<sql.widgets.table_widget.table_widget.TableWidget at 0x13c6731f0>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sqlcmd explore --table {{table_name}}"
   ]
  }
 ],
 "metadata": {
  "jupytext": {
   "notebook_metadata_filter": "myst"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.13"
  },
  "myst": {
   "html_meta": {
    "description lang=en": "Templatize SQL queries in Jupyter via JupySQL",
    "keywords": "jupyter, sql, jupysql, jinja",
    "property=og:locale": "en_US"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
